# -*- coding: utf-8 -*-
"""
与stock数据库通信的模块
以后如果改用其他的数据库，只需要改这个文件的函数就可以了，而不需要在每个模块都改对应的代码。
"""
import pymysql
from DBUtils.PooledDB import PooledDB


class Gmysql:
    """mysql连接参数"""
    pools = ""


class mysql(object):
    """连接mysql的类，使用连接池.
    使用方法：
    import pandas as pd
    from tools.database import stock_db
    mysql = stock_db.mysql()
    sql = "select 1"
    pd.read_sql(sql, conn)
    # 最后记得关闭连接
    mysql.close()
    """

    def __init__(self, user="root", passwd="123456", ip="127.0.0.1", db="mysql"):
        self.user = user
        self.password = passwd
        self.host = ip
        self.db = db
        self.conn = mysql.get_conn(self)
        self.cursor = self.conn.cursor()

    def get_conn(self):
        """连接数据库"""
        if Gmysql.pools == "":
            Gmysql.pools = PooledDB(pymysql, mincached=1, host=self.host, user=self.user, passwd=self.password,
                                    db=self.db)
        return Gmysql.pools.connection()

    def execute(self, sql):
        """执行sql语句"""
        self.cursor.execute(sql)
        self.conn.commit()

    def close(self):
        """如果不用了，要记得关闭连接"""
        self.cursor.close()
        self.conn.close()

    def delete_today_data(self, table="", date="", code=""):
        """数据入库之前要先删除当天的数据.
        注意，要求日期字段是字符型，名字是dates"""
        if code == "":
            sql = "delete from %s where dates='%s' " % (table, date)
        else:
            sql = "delete from %s where code='%s' and dates='%s' " % (table, date, code)
        self.cursor.execute(sql)
        self.conn.commit()
        return sql

    def df_into_db(self, table, df, types="insert"):
        """将df导入mysql数据库"""
        if len(df) == 0:
            return None
        # 将全部数据转成字符串格式
        tostr = lambda s: str(s) if s else ''
        df2 = df.applymap(tostr)
        # 判断是否需要清空表
        if types.upper() in "TRUNCATE,DELETE":
            self.execute("delete from %s" % table)
        # 转成list_tuple格式，才能导入数据库
        param = []
        for i in range(len(df2)):
            param.append(tuple(df2.iloc[i]))
        # 创建入库的sql
        sql = "insert into table_name (columns) values (s_many_times) "
        columns = ','.join(list(df2.columns))
        s_many_times = ['%s' for i in range(len(df2.columns))]
        s_many_times = ','.join(s_many_times)
        sql = sql.replace('table_name', table)
        sql = sql.replace('columns', columns)
        sql = sql.replace('s_many_times', s_many_times)
        # 入库
        self.cursor.executemany(sql, param)
        self.conn.commit()


def connect(host='127.0.0.1', user='hr', passwd='123456', db='stock', port=3306):
    '''连接mysql数据库'''
    conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset="utf8")
    return conn


def create_table_sql(tb_name, df):
    '''拼接create_table的sql语句。
    tb_name:表名
    df:要求有 column,types,context(注释) '''
    sql = "drop table tb_name;\ncreate table tb_name (\ncolumn_types_context\n)\n;\ncommit;\n"
    # 列名转大写
    df.columns = [col.upper() for col in df.columns]
    #
    df['TMP'] = df['COLUMN'] + ' ' + df['TYPES'] + " comment '" + df['CONTEXT'] + "'\n"
    col_str = ','.join(df['TMP'])
    sql = sql.replace('column_types_context', col_str)
    sql = sql.replace('tb_name', tb_name)
    # 将建表的sql写到文件备份
    file = 'create_ ' + tb_name + ' .sql'
    with open(file, 'w', encoding='utf-8') as f:
        f.writelines(sql)
    return sql


def main():
    pass


if __name__ == "__main__":
    main()
